package com.example.demo.dao.impl;

import com.example.demo.dao.BaseDaoImpl;
import com.example.demo.dao.IAccountDao;
import com.example.demo.model.po.AccountPage;
import com.example.demo.model.request.AccountPageRequest;
import com.example.demo.util.AssertUtils;
import com.example.demo.util.DateUtils;
import org.jooq.Condition;
import org.jooq.Record;
import org.jooq.SelectJoinStep;
import org.springframework.stereotype.Component;

import java.util.ArrayList;
import java.util.List;

import static com.example.demo.jooq.tables.Account.ACCOUNT;
import static com.example.demo.jooq.tables.Address.ADDRESS;
import static com.example.demo.jooq.tables.Virtual.VIRTUAL;
import static com.example.demo.jooq.tables.VisaCard.VISA_CARD;
import static com.example.demo.jooq.tables.Vps.VPS;
import static org.jooq.impl.DSL.count;

@Component
public class IAccountDaoImpl extends BaseDaoImpl implements IAccountDao {

    @Override
    public List<AccountPage> queryPageAccountList(AccountPageRequest accountPageRequest) {
        //查询条件
        List<Condition> conditions = new ArrayList<>();
        if(AssertUtils.isNotEmpty(accountPageRequest.getKeyword())){
            conditions.add(ACCOUNT.EMAIL.likeRegex(accountPageRequest.getKeyword().trim())
                    .or(ACCOUNT.CHARGE_USER_NAME.likeRegex(accountPageRequest.getKeyword().trim()))
                    .or(VIRTUAL.VIRTUAL_NAME.likeRegex(accountPageRequest.getKeyword().trim()))
            );
        }
        if(AssertUtils.isNotEmpty(accountPageRequest.getAccountStatus())){
            conditions.add(ACCOUNT.ACCOUNT_STATUS.eq(accountPageRequest.getAccountStatus()));
        }
        if(AssertUtils.isNotEmpty(accountPageRequest.getCreatedDate())){
            conditions.add(ACCOUNT.CREATED_DATE.eq(DateUtils.stringToTime(accountPageRequest.getCreatedDate())));
        }
        if(AssertUtils.isNotEmpty(accountPageRequest.getSite())){
            conditions.add(ACCOUNT.SITE.eq(accountPageRequest.getSite()));
        }
        if(AssertUtils.isNotEmpty(accountPageRequest.getStar())){
            conditions.add(ACCOUNT.STAR.eq(accountPageRequest.getStar()));
        }
        if(AssertUtils.isNotEmpty(accountPageRequest.getAccountUseType())){
            conditions.add(ACCOUNT.ACCOUNT_USE_TYPE.eq(accountPageRequest.getAccountUseType()));
        }

        SelectJoinStep<Record> totalLine = this.getDslContext()
                .select(ACCOUNT.fields())
                .select(VIRTUAL.SERVICE_NAME,VIRTUAL.SERVICE_USER,VIRTUAL.SERVICE_PASSWORD,VIRTUAL.VIRTUAL_NAME,VIRTUAL.VIRTUAL_LOCAL_IP)
                .select(VISA_CARD.VISA_CARD_NO,VISA_CARD.VISA_CHANNEL,VISA_CARD.VISA_TYPE,VISA_CARD.END_DATE,VISA_CARD.CVC,VISA_CARD.TOTAL_AMOUNT,VISA_CARD.BALANCE_AMOUNT,VISA_CARD.USE_AMOUNT)
                .select(VPS.VPS_NAME,VPS.VPS_IP,VPS.CHANNEL,VPS.CHANNEL_URL,VPS.CHANNEL_USER)
                .select(ADDRESS.NAME,ADDRESS.COUNTRY,ADDRESS.PHONE,ADDRESS.ADDRESS_,ADDRESS.ADDRESS_ID,ADDRESS.STATE,ADDRESS.ZIPCODE,ADDRESS.CITY)
                .select(count().over().as("totalLine"))
                .from(ACCOUNT)
                .leftJoin(VIRTUAL).on(ACCOUNT.VIRTUAL_ID.eq(VIRTUAL.VIRTUAL_ID))
                .leftJoin(VISA_CARD).on(ACCOUNT.VISA_CARD_ID.eq(VISA_CARD.VISA_CARD_ID))
                .leftJoin(VPS).on(VPS.VPS_ID.eq(ACCOUNT.VPS_ID))
                .leftJoin(ADDRESS).on(ACCOUNT.ADDRESS_ID.eq(ADDRESS.ADDRESS_ID));
        totalLine.where(conditions);
        return totalLine.orderBy(ACCOUNT.CREATED_DATE.desc())
                .offset(accountPageRequest.getOffset())
                .limit(accountPageRequest.getPageSize())
                .fetchInto(AccountPage.class);
    }

    @Override
    public AccountPage queryAccountDetail(String accountId) {
       return this.getDslContext()
                .select(ACCOUNT.fields())
                .select(VIRTUAL.VIRTUAL_ID,VIRTUAL.SERVICE_NAME,VIRTUAL.SERVICE_USER,VIRTUAL.SERVICE_PASSWORD,VIRTUAL.VIRTUAL_NAME,VIRTUAL.VIRTUAL_LOCAL_IP)
                .select(VISA_CARD.VISA_CARD_ID,VISA_CARD.VISA_CARD_NO,VISA_CARD.VISA_CHANNEL,VISA_CARD.VISA_TYPE,VISA_CARD.END_DATE,VISA_CARD.CVC,VISA_CARD.TOTAL_AMOUNT,VISA_CARD.BALANCE_AMOUNT,VISA_CARD.USE_AMOUNT)
                .select(VPS.VPS_ID,VPS.VPS_NAME,VPS.VPS_IP,VPS.CHANNEL,VPS.CHANNEL_URL,VPS.CHANNEL_USER,VPS.SSH_USER,VPS.SSH_PASSWORD)
                .select(ADDRESS.NAME,ADDRESS.COUNTRY,ADDRESS.PHONE,ADDRESS.ADDRESS_,ADDRESS.ADDRESS_ID,ADDRESS.STATE,ADDRESS.ZIPCODE,ADDRESS.CITY)
               .from(ACCOUNT)
                .leftJoin(VIRTUAL).on(ACCOUNT.VIRTUAL_ID.eq(VIRTUAL.VIRTUAL_ID))
                .leftJoin(VISA_CARD).on(ACCOUNT.VISA_CARD_ID.eq(VISA_CARD.VISA_CARD_ID))
                .leftJoin(VPS).on(VPS.VPS_ID.eq(ACCOUNT.VPS_ID))
                .leftJoin(ADDRESS).on(ACCOUNT.ADDRESS_ID.eq(ADDRESS.ADDRESS_ID))
                .where(ACCOUNT.ACCOUNT_ID.eq(accountId)).fetchOneInto(AccountPage.class);
    }
}
